Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 29
Providing for Data Integrity and Triggers

When designing an application or a database, it is important that you design not only for performance and functionality but for data integrity as well. A database that does not guarantee data integrity is not worth having. The database and application are only as good as the data stored within them.

An accounting application that does not guarantee that the books balance does not have much value to the accountant who uses it. It is important to guarantee the integrity of your database. By placing integrity constraints on your database to enforce business rules, you guarantee that these rules are not violated.

It is very difficult to foresee every possible event in an application; it is even more difficult to foresee the outcome of ad-hoc changes to the database. By using integrity constraints, your data will be protected. This chapter looks at some ways you can protect your data—and how to do this in an optimal manner.

Integrity Constraints

It is usually necessary to enforce business rules in your application and database to protect your data. By using the Oracle integrity constraints, you can protect your data efficiently. Enforcing business rules from within Oracle can be more efficient than enforcing the same rules within your application for several reasons:

  SQL statements can be reduced. By allowing Oracle to enforce these business rules with integrity constraints, the application does not have to issue additional SQL statements. This reduces application overhead and network traffic.
  Internal operations are faster. Because integrity constraints are internal operations, they are naturally faster and more efficient.
  Application development is simplified. When you enforce the business rules within the database, multiple applications or parts of the same application need not duplicate the logic of the business rules.

This is not to say that the entire application has to rely on internal Oracle operations for data validations and bounds checking. In some cases, the application itself can handle these operations more efficiently; in general, however, Oracle integrity constraints are more efficient.

The following sections explain how Oracle integrity constraints can be used to enforce business rules and ensure data integrity.

Referential Integrity

Possibly the most common use of constraints is to enforce referential integrity. Referential integrity is used to guarantee that a column value that references another table exists in the other table. Referencing a value in another table that does not exist can cause major data integrity problems. Consider the following example.

The DOGS table introduced in earlier chapter contains a column that describes the breed of the dog (see Table 29.1). This value is actually a numeric value that references a row in the BREEDS table (see Table 29.2). If the DOGS table references a value for the dog’s breed that does not exist in the BREEDS table, an unknown result occurs.

Table 29.1 The DOGS Table

DOGNAME AGE BREED OWNER

Dash 6 1 Jones
Chip 4 1 Jones
Rigby 3 3 Smith
Duncan 5 2 Miller
Rufus 5 3 Smith
Splash 3 4 Blake
Piper 6 1 Turner
Pierce 6 1 King
Shasta 9 5 Adams
Teller 1 1 King
Spots 3 2 Ward
B.J. 3 4 Allan
Ginger 4 5 Turner
Jessie 10 7 Wilson
Ruff 9 8 King
Velvet 8 9 Wilson
Ty 4 10 Jones
Cotton 6 8 Atkins
Angel 6 8 McArthur
Provo 5 6 Smith
Jenny 7 6 Durell
Daphne 6 7 Bench
Bug 3 5 Durell
Sammie 8 3 Turner
Bubba 3 4 Pike

Table 29.2 The BREEDS Table

BREED BREED NAME DESCRIPTION

1 Border Collie Very intelligent and happy
2 Sheltie Energetic and willing to please
3 Jack Russell Terrier High energy and active
4 Golden Retriever Loves to play
5 All American (Mix) Anything at all
6 Great Pyrenees Big and friendly
7 Irish Setter Hunting instinct
8 Toy Poodle Lap dog
9 Beagle Good nose for tracking
10 Greyhound Fast and sleek


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.